About the Spreadsheet Web Part

The Spreadsheet Web Part provides a way to create custom Web Parts that can be bound to external data sources such as Microsoft SQL Server, Microsoft Business Solutions (Great Plains), or Microsoft Windows SharePoint Services. When bound to an external data source, the Spreadsheet Web Part binds and retrieves data from these data sources by using a data retrieval service (an XML Web Service that resides on a Windows SharePoint Services server for connecting to and retrieving data). Once the data is retrieved, the Spreadsheet Web Part displays the queried data by mapping its XML elements to the spreadsheet grid.

The Spreadsheet Web Part can also implement a set of Web Part connection interfaces that allow it to interact with other Web Parts that support connection interfaces. When two Web Parts are connected, a user can perform an action in one Web Part to affect the contents of the other Web Part. For example, clicking a row in a connected Orders Web Part can filter the display of related sales data in a Product Sales Web Part.

Required Files and Components

A Spreadsheet Web Part requires five supporting files and components to run:

• The Spreadsheet Web Part assembly   A software component that resides on a Windows SharePoint Services site to provide the server-side functionality for all of the Spreadsheet Web Parts on that site. Before a Windows SharePoint Services site can support Spreadsheet Web Parts, the site administrator must install the Microsoft Office 2003 Web Parts and Components on that site. Office 2003 Web Parts and Components is available from the Office Update Download Center. This installation needs to be performed only once to support any number of Spreadsheet Web Parts on that site.
• An XML Spreadsheet file   To define the data binding, XML mapping, layout, formatting, data validations, and calculations for a Spreadsheet Web Part, an XML Spreadsheet file created in Microsoft Excel is required. You can omit the XML Spreadsheet file if you want to create a blank spreadsheet.
• A solution specification file   An XML file that contains elements that define various settings, such as the location of the XML Spreadsheet file, declarative definitions of connection interfaces, HTML and script, the ability to retrieve the values of server properties, and whether the user interface commands that allow users to connect to data, save the layout, and edit or delete queries are hidden or disabled. You can omit the solution specification file if you want to create a blank spreadsheet.
• A Web Part definition file   An XML file saved with a .dwp file extension that specifies the Spreadsheet Web Part assembly and the default settings for Web Part properties, such as the Title and Description properties. It also specifies the SolutionFileLocation property, which is the URL to the solution specification file. A Web Part definition file is used to import a Spreadsheet Web Part into a Web Part page or Web Part catalog.
• The Spreadsheet Component   An ActiveX component that must be installed on a user's computer to support the client-side functionality of a Spreadsheet Web Part. The Spreadsheet Component is installed as part of the Office 2003 Web Components, which are installed by default along with Office 2003. You can also download and install the Office 2003 Web Components separately from Office 2003, but users opening the Spreadsheet Web Part will experience reduced functionality if they don't already have Office 2003 installed, or have access to an Office 2003 software license.

Creating and Deploying

You can create a simple Spreadsheet Web Part without using Excel by adding one directly to a Web Part page from the Web Part gallery on a Windows SharePoint Services site by clicking Modify My Page or Modify Shared Page (depending on whether you are viewing the page in Personal View or Shared View), and then clicking Add Web Parts. Once you have a Spreadsheet Web Part on a Web Part page, you can connect it to a data source by clicking Connect to Data on the toolstrip.

To create and deploy a more complex Spreadsheet Web Part, you need to perform several procedures before you can use the Web Part on a Web Part page:

• Create the XML Spreadsheet file:
  • Using the Data Connection Wizard in Excel, specify a data source and define the data that you want to bind to and retrieve.
  • Excel will map your imported data as a list of repeating rows by default, or you can also use Excel's XML Source task pane to map XML elements to the spreadsheet grid.
  • Using other Excel commands, format cells and define formulas or calculated columns.
  • After you are finished working in Excel, save your data binding and layout definition as an XML Spreadsheet file to a document library on the same Windows SharePoint Services server where you will be using your Spreadsheet Web Part.
• Create the solution specification file in a text editor and save it to a document library on the same Windows SharePoint Services server where you will be using your Spreadsheet Web Part.
• Create the Web Part definition file in a text editor, and then use it to import your Spreadsheet Web Part into a Web Part page or Web Part catalog.

Note  You can make the process of creating a Spreadsheet Web Part simpler by using the Spreadsheet Web Part Add-in for Microsoft Office Excel 2003. This add-in is available for download from the Microsoft Download Center Web Site.